package HQU.web.action;

import HQU.bean.Emp;
import HQU.utils.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@WebServlet({"/emp/list","/emp/delete","/emp/modify","/emp/detail","/emp/save"})
public class empServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String servletPath = request.getServletPath();
        if ("/emp/list".equals(servletPath)){
            doList(request, response);
        }else if ("/emp/delete".equals(servletPath)){
            doDel(request,response);
        }else if ("/emp/modify".equals(servletPath)){
            doModify(request,response);
        }else if ("/emp/detail".equals(servletPath)){
            doDetail(request,response);
        }else if ("/emp/save".equals(servletPath)){
            doSave(request,response);
        }
    }

    private void doSave(HttpServletRequest request, HttpServletResponse response) throws IOException {
        request.setCharacterEncoding("UTF-8");
        //获取表单数据
        String employee_id = request.getParameter("employee_id");
        String name = request.getParameter("name");
        String phone_number = request.getParameter("phone_number");
        String job_id = request.getParameter("job_id");
        String salary = request.getParameter("salary");

        // 连接数据库执行insert语句
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtil.getConnection();
            String sql = "insert into employees(name,phone_number,job_id,salary)values (?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,phone_number);
            ps.setString(3,job_id);
            ps.setString(4,salary);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn,ps,null);
        }
        if (count == 1){
            response.sendRedirect(request.getContextPath() + "/emp/list");
        }
    }

    private void doDetail(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Emp emp = new Emp();
        String employee_id = request.getParameter("employee_id");
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select name, phone_number, job_id, salary from employees where employee_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, employee_id);
            rs = ps.executeQuery();
            if (rs.next()){
                String name = rs.getString("name");
                String phone_number = rs.getString("phone_number");
                int job_id = rs.getInt("job_id");
                int salary = rs.getInt("salary");
                emp.setEmployee_id(Integer.parseInt(employee_id));
                emp.setName(name);
                emp.setPhone_number(phone_number);
                emp.setJob_id(job_id);
                emp.setSalary(salary);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn,ps,rs);
        }
        request.setAttribute("emp",emp);
        request.getRequestDispatcher("/" + request.getParameter("f") + ".jsp").forward(request, response);

    }

    private void doModify(HttpServletRequest request, HttpServletResponse response) throws IOException {
        request.setCharacterEncoding("UTF-8");
        //获取表单数据
        String employee_id = request.getParameter("employee_id");
        String name = request.getParameter("name");
        String phone_number = request.getParameter("phone_number");
        String job_id = request.getParameter("job_id");
        String salary = request.getParameter("salary");

        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtil.getConnection();
            String sql = "update employees set name = ?, phone_number = ?, job_id = ?, salary = ? where employee_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,phone_number);
            ps.setString(3,job_id);
            ps.setString(4,salary);
            ps.setString(5,employee_id);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn, ps, null);
        }
        if (count == 1){
            response.sendRedirect(request.getContextPath() + "/emp/list");
        }
    }

    private void doDel(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String employee_id = request.getParameter("employee_id");
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from employees where employee_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, employee_id);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn,ps,null);
        }
        if (count == 1){
            String contextPath = request.getContextPath();
            response.sendRedirect(contextPath + "/emp/list");
        }
    }

    private void doList(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        List<Emp> emps = new ArrayList<>();
        //连接数据库，查询所有的员工信息
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = DBUtil.getConnection();
            String sql = "select employee_id, name, phone_number, job_id, salary from employees";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            //遍历结果
            while (rs.next()){
                int employee_id = rs.getInt("employee_id");
                String name = rs.getString("name");
                String phone_number = rs.getString("phone_number");
                int job_id = rs.getInt("job_id");
                int salary = rs.getInt("salary");
                Emp emp = new Emp();
                emp.setPhone_number(phone_number);
                emp.setEmployee_id(employee_id);
                emp.setName(name);
                emp.setJob_id(job_id);
                emp.setSalary(salary);
                emps.add(emp);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(conn,ps,rs);
        }
        request.setAttribute("empList", emps);
        request.getRequestDispatcher("/list.jsp").forward(request,response);
    }
}